from ast import main
from selenium.webdriver import Keys
from selenium.webdriver.common.by import By
import allure
import csv
import logging
from util.db_util import DBUtil


def none_to_empty(value):
    return value if value is not None else ''


def y_n_to_yes_no(value):
    if value == 'Y':
        return 'Yes'
    elif value == 'N':
        return 'No'
    return value


def assert_equal_with_allure(label, actual, expected):
    with allure.step(f"Validation: {label} - Expected='{expected}', Actual='{actual}'"):
        assert actual == expected, f"{label} mismatch. Actual: {actual}, Expected: {expected}"


def normalize_number(value_str):
    """
    标准化数字字符串，去掉小数点后多余的0
    
    Args:
        value_str: 字符串值
        
    Returns:
        str: 标准化后的字符串
    """
    try:
        # 尝试转换为浮点数
        float_val = float(value_str)
        # 如果是整数，返回整数形式
        if float_val == int(float_val):
            return str(int(float_val))
        # 否则返回浮点数形式，去掉多余的0
        return str(float_val).rstrip('0').rstrip('.')
    except (ValueError, TypeError):
        # 如果不是数字，直接返回原字符串
        return value_str


def assert_csv_to_db_with_details_with_allure(csv_file_path, db_table_name, identifier, module="CALCULATION"):
    """
    带详细信息的数据断言方法

    Args:
        csv_file_path (str): CSV文件路径
        db_table_name (str): 数据库表名
        identifier (int): 标识符（可以是file_id, process_id等）
        module (str): 模块类型，默认为"CALCULATION"

    Returns:
        dict: 包含详细比较结果的字典
    """
    result = {
        "success": False,
        "error_message": "",
        "csv_row_count": 0,
        "db_row_count": 0,
        "mismatch_details": []
    }

    try:
        # 读取CSV数据
        csv_data = []
        with open(csv_file_path, 'r', encoding='utf-8') as csvfile:
            csv_reader = csv.reader(csvfile, delimiter='|')
            headers = next(csv_reader)  # 读取列名但不跳过，用于日志输出
            logging.info(f"CSV headers: {headers}")
            for row in csv_reader:
                csv_data.append(row)

        result["csv_row_count"] = len(csv_data)
        logging.info(f"Read {result['csv_row_count']} rows from CSV file: {csv_file_path}")

        # 构建查询SQL
        if module.upper() == "ETL":
            sql = f"SELECT * FROM {db_table_name} WHERE File_ID = ?"
            id_field = "File_ID"
        else:
            sql = f"SELECT * FROM {db_table_name} WHERE PROCESS_ID = ? ORDER BY DATA_ID"
            id_field = "PROCESS_ID"

        logging.info(f"Executing SQL: {sql} with {id_field}={identifier}")

        # 查询数据库数据
        db_rows = DBUtil.select_all(sql, (identifier,))

        if not db_rows:
            result["error_message"] = f"数据库中没有找到 {id_field}={identifier} 的数据"
            logging.warning(result["error_message"])
            with allure.step(
                    f"Assert CSV to DB with details: \n db_table_name: {db_table_name}, \n identifier: {identifier}, \n csv_file_path: {csv_file_path}"):
                # 直接抛出异常，确保测试失败
                raise AssertionError(f"数据断言失败: {result['error_message']}")
            return result

        result["db_row_count"] = len(db_rows)
        logging.info(f"Retrieved {result['db_row_count']} rows from database")

        # 处理数据库数据（去掉前两列：通常是ID和File_ID/PROCESS_ID）
        db_data = []
        for row in db_rows:
            # 确保行数据足够长
            if len(row) >= 2:
                processed_row = row[2:]  # 去掉前两列
            else:
                processed_row = row  # 如果行太短，保留原样
            db_data.append(processed_row)

        # 比较数据行数
        if len(csv_data) != len(db_data):
            result["error_message"] = f"数据行数不匹配：CSV文件 {len(csv_data)} 行，数据库 {len(db_data)} 行"
            logging.error(result["error_message"])
            with allure.step(
                    f"Assert CSV to DB with details: \n db_table_name: {db_table_name}, \n identifier: {identifier}, \n csv_file_path: {csv_file_path}"):
                # 直接抛出异常，确保测试失败
                raise AssertionError(f"数据断言失败: {result['error_message']}")
            return result

        # 逐行比较
        mismatch_count = 0
        for i, (csv_row, db_row) in enumerate(zip(csv_data, db_data), 1):
            # 检查列数是否匹配
            if len(csv_row) != len(db_row):
                result["error_message"] = f"第 {i} 行列数不匹配：CSV {len(csv_row)} 列，数据库 {len(db_row)} 列"
                logging.error(result["error_message"])
                with allure.step(
                        f"Assert CSV to DB with details: \n db_table_name: {db_table_name}, \n identifier: {identifier}, \n csv_file_path: {csv_file_path}"):
                    # 直接抛出异常，确保测试失败
                    raise AssertionError(f"数据断言失败: {result['error_message']}")
                return result

            # 逐列比较
            for j, (csv_value, db_value) in enumerate(zip(csv_row, db_row)):
                csv_str = normalize_number(str(csv_value).strip())
                db_str = normalize_number(str(db_value).strip())

                if csv_str != db_str:
                    mismatch = {
                        "row": i,
                        "column": j + 1,
                        "column_name": headers[j] if j < len(headers) else f"Column_{j + 1}",
                        "expected": csv_str,
                        "actual": db_str
                    }
                    result["mismatch_details"].append(mismatch)
                    mismatch_count += 1

                    # 限制日志数量，避免过多日志
                    if mismatch_count <= 10:
                        logging.warning(
                            f"数据不匹配 - 行{i} 列{j + 1} ({headers[j] if j < len(headers) else f'Column_{j + 1}'}): "
                            f"期望='{csv_str}', 实际='{db_str}'")

        # 如果没有不匹配的项，则成功
        if not result["mismatch_details"]:
            result["success"] = True
            result["error_message"] = "数据断言通过"
            logging.info("数据断言通过，CSV与数据库数据一致")
            with allure.step(
                    f"Assert CSV to DB with details: \n db_table_name: {db_table_name}, \n identifier: {identifier}, \n csv_file_path: {csv_file_path}"):
                # 成功情况下的步骤记录
                pass
        else:
            result["error_message"] = f"数据断言失败，发现 {len(result['mismatch_details'])} 处不匹配"
            logging.error(f"数据断言失败，发现 {len(result['mismatch_details'])} 处不匹配")
            if mismatch_count > 10:
                logging.warning(f"仅显示前10条不匹配信息，总共 {mismatch_count} 条")

            with allure.step(
                    f"Assert CSV to DB with details: \n db_table_name: {db_table_name}, \n identifier: {identifier}, \n csv_file_path: {csv_file_path}"):
                # 直接抛出异常，确保测试失败
                raise AssertionError(f"数据断言失败: {result['error_message']}")

        return result

    except FileNotFoundError as e:
        error_msg = f"CSV文件不存在: {csv_file_path}"
        logging.error(error_msg)
        with allure.step(
                f"Assert CSV to DB with details: \n db_table_name: {db_table_name}, \n identifier: {identifier}, \n csv_file_path: {csv_file_path}"):
            raise AssertionError(f"数据断言失败: {error_msg}")
    except Exception as e:
        error_msg = f"数据断言过程中发生异常：{str(e)}"
        logging.error(error_msg, exc_info=True)
        with allure.step(
                f"Assert CSV to DB with details: \n db_table_name: {db_table_name}, \n identifier: {identifier}, \n csv_file_path: {csv_file_path}"):
            raise AssertionError(f"数据断言失败: {error_msg}")